package com.zzl.procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

public class TestProcedure {
	
	public static Connection getMysqlConnection() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test";// 要操作的数据库名称
		String username = "root";// 数据库用户名
		String password = "root";// 密码
		return getConnection(driver, url, username, password);
	}

	public static Connection getConnection(String driver, String url, String userName,
			String passwd) {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, userName, passwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void testMysqlProcedureRtnInt(Connection con, CallableStatement cs,
			int limit) throws Exception {
		cs = con.prepareCall("{call proc_mysql_getsum_bywhile(?,?)}");
		// 设置参数
		cs.setInt(1, limit);
		// 注册输出参数
		cs.registerOutParameter(2, Types.INTEGER);
		// 执行过程
		cs.execute();
		// 获取结果
		int result = cs.getInt(2);
		System.out.println("结果为:" + result);
	}
	
	
	public static void testMysqlProcedureRtnVarchar(Connection con,
			CallableStatement cs, int id) throws Exception {
		cs = con.prepareCall("{call proc_mysql_inout_test2(?,?)}");
		// 设置参数
		cs.setInt(1, id);
		// 注册输出参数
		cs.registerOutParameter(2, Types.VARCHAR);
		// 执行过程
		cs.execute();
		// 获取结果
		String result = cs.getString(2);
		System.out.println("结果为:" + result);
	}
	
	
	public static void testMysqlProcedureRtnCursor(Connection con,
			CallableStatement cs, ResultSet rs) throws Exception {
		cs = con.prepareCall("{call proc_mysql_return_cursor_method2()}");
		// 执行过程
		rs = cs.executeQuery();
		System.out.println("id" + "\t" + "username" + "\t" + "passwd");
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t"
					+ rs.getString(3));
		}
	}
	
	
	
	
	public static void main(String[] args) {
		Connection con = getMysqlConnection();
		CallableStatement cs = null ;
		ResultSet rs = null;
		try {
			//			testMysqlProcedureRtnInt(con, cs, 10);
//			testMysqlProcedureRtnVarchar(con, cs, 2);
			testMysqlProcedureRtnCursor(con, cs, rs);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


}
